# -*- encoding: utf-8 -*-
import datetime

from odoo import models, fields, api, _
from odoo.addons import base_cw
from odoo.exceptions import UserError
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT as DTF


class AccountBalanceSettle(models.Model):
    _name = 'account.balance.settle'
    _description = u'总帐月结处理'
    _rec_name = 'period_id'

    period_id = fields.Many2one('account.period', u'期别', ondelete="restrict", domain="[('state','!=','done')]")
    to_date = fields.Date(u'月结截至日期')
    state = fields.Selection([('draft', u'开启'), ('done', u'关闭'), ],
                             u'期别状态', readonly=True, related='period_id.state')
    note = fields.Text(u'备注')
    line_ids = fields.One2many('account.balance', 'master_id', u'已审核会科余额',domain="[('state','=','audited')]")
    unpass_line_ids = fields.One2many('account.balance', 'master_id', u'未审核会科余额',domain="[('state','!=','audited')]")
    result_ids = fields.One2many('account.balance.settle.line', 'master_id', string='检测结果')


    def _default_rule_id(self):
        return self.env.ref('cncw_ledger.account_balance_rule_default')

    rule_id = fields.Many2one('account.balance.settle.rule', string=u'数据校验规则', default=_default_rule_id)

    _sql_constraints = [('period_id_unique', 'unique (period_id)', u'期别不能重复!')]

    @api.onchange('period_id')
    def onchange_period_id(self):
        self.to_date = self.period_id and self.period_id.date_stop or None

    @api.onchange('to_date')
    def onchange_to_date(self):
        if self.to_date < self.period_id.date_start or self.to_date > self.period_id.date_stop:
            raise UserError(_(u'月结截至日期 需在期别之内!'))

    def action_balance_settle(self):
        pre = self.env['account.period'].previous(self.period_id)
        pre_period_id = pre and pre.id or 0
        self._cr.execute('select account_balance_settle_unpass(%s,%s,%s,%s,%s)',
                         (self.period_id.id, pre_period_id, self.id, self.to_date, self._uid))

        self.check_data()
        self._cr.execute('select account_balance_settle(%s,%s,%s,%s,%s)',
                         (self.period_id.id, pre_period_id, self.id, self.to_date, self._uid))
        # 查询部门费用明细
        self._cr.execute('select department_expense_summary_settle(%s,%s)',
                         (self.period_id.id,self._uid))

    def init(self):
        # account_three_columns_settle
        self._cr.execute("""
CREATE OR REPLACE FUNCTION public.account_three_columns_settle(
    period_id_t integer,
    uid integer)
  RETURNS void AS
$BODY$
/*
  DROP FUNCTION account_three_columns_settle(integer,integer)
  select account_three_columns_settle(14,1)
  select *  from account_period order by id
    update account_voucher a set period_id=b.id
    from account_period b where a.date between date_start and date_stop
    三栏帐
*/
DECLARE
    begin_amount_t numeric;
    end_t          numeric;
    rec            record;
    rec_balance    record;
    seq            integer;
begin
    -- 创建凭证明细行
    create temp table account_voucher_line_t
    (
        id             integer, -- 期别
        account_id     integer, -- 会计科目
        balance_id     integer, -- 科目余额表
        sub_account_id integer,
        debit          numeric, -- 本期借方(原币)
        credit         numeric, -- 本期贷方(原币)
        dc_type        character varying,
        report_seq     integer, -- 序号
        balance        numeric, -- 期末余额(原币)
        date           date
    );
    -- 插入数据到凭证明细行
    insert into account_voucher_line_t(id, account_id, sub_account_id, debit, credit, dc_type, report_seq, balance,
                                       date)
    select a.id,
           a.account_id,
           d.sub_account_id,
           sum(coalesce(debit, 0))  as debit,
           sum(coalesce(credit, 0)) as credit,
           c.dc_type,
           0,
           0,
           b.date
    -- 凭证明细
    from account_voucher_line a
             -- 会计凭证
             left join account_voucher b on a.master_id = b.id
             -- 会计科目
             left join cncw_account c on a.account_id = c.id
             -- 多辅助核算中间表
             left join sub_account_line d on a.id = d.voucher_line_id
    --  会计凭证状态已确认、已审核
    where b.state in ('confirmed', 'audited')
      -- 会计期间
      and b.period_id = period_id_t
    group by a.id, a.account_id, d.sub_account_id, c.dc_type, b.date
    order by b.date, a.id;
    -- 更新凭证明细
    update account_voucher_line a
    set report_seq=0,
        balance_id=null,
        balance=0
    from account_voucher_line b
    --  看不懂
    where a.id = b.id;

    -- 遍历该期间内的科目余额表
    for rec_balance in select a.id as balance_id, account_id, period_id, a.begin as begin_amount
                       -- 科目余额表
                       from account_balance a --left join cncw_account c on a.account_id=c.id
                       where period_id = period_id_t
                         and account_id is not null
                         and a.sub_account_type <> 'view'
                         and sub_account_id is null
        loop
            seq := 0;
            -- 期初金额
            begin_amount_t := rec_balance.begin_amount;
            -- 步长
            seq := seq + 1;
            for rec in select id, account_id, sub_account_id, debit, credit, dc_type
                       -- 凭证明细模版
                       from account_voucher_line_t a
                       -- 明细行会计科目ID = 科目余额表会计科目ID
                       where account_id = rec_balance.account_id
                       order by a.date, id
                loop
                    -- 期初  + 借方 - 贷方
                    end_t := coalesce(begin_amount_t, 0) + (coalesce(rec.debit, 0) - coalesce(rec.credit, 0));

                    update account_voucher_line_t
                    set report_seq=seq,
                        balance=end_t,
                        balance_id=rec_balance.balance_id
                    where id = rec.id;
                    seq := seq + 1;
                    begin_amount_t := end_t;
                end loop;
        end loop;

    update account_voucher_line a
    set report_seq=b.report_seq,
        balance=b.balance,
        balance_id=b.balance_id
    from account_voucher_line_t b
    where a.id = b.id;
    drop table account_voucher_line_t;
end;
 $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
""")

        self._cr.execute("""
CREATE OR REPLACE FUNCTION public.account_balance_settle(
    period_id_t integer,
    pre_period_id_t integer,
    master_id_t integer,
    to_date timestamp without time zone,
    uid integer)
  RETURNS void AS
$BODY$
                -- DROP FUNCTION account_balance_settle(integer,integer,integer,timestamp ,integer)
                -- select account_balance_settle(14,12,1,'2017-01-31',1)
                --select *  from account_period order by id
                -- drop table account_balance
                DECLARE
                    from_date timestamp;
                    year_t character varying;   --年
                    rec record;
                begin
                    select substring(name,4),date_start into year_t,from_date from account_period where id=period_id_t;

                    create temp table account_balance_t(
                      period_id integer, -- 期别
                      master_id integer, -- 总帐月结处理
                      account_id integer, -- 会计科目
                      dc_type  character varying, -- 借贷
                      sub_account_id integer, -- 辅助核算
                      sub_account_type character varying, -- 会科类型
                      parent_id integer, -- 上级科目
                      begin numeric, -- 期初金额(原币)
                      debit numeric, -- 本期借方(原币)
                      credit numeric, -- 本期贷方(原币)
                      balance numeric, -- 期末余额(原币)

                      a1 numeric,--当期(余额)
                      b1 numeric,--前一期(余额)
                      c1 numeric,--前二期(余额)
                      d1 numeric,--去年同期(余额)
                      p1 numeric,--前年同期(余额)

                      e1 numeric,--年初余额
                      f1 numeric,--去年年初余额
                      s1 numeric,--前年年初余额

                      g1 numeric,--年累余额
                      h1 numeric,--去年年累余额
                      q1 numeric,--前年年累余额

                      i1 numeric,--当期(借-贷)
                      j1 numeric,--前一期(借-贷)
                      k1 numeric,--前二期(借-贷
                      l1 numeric,--去年同期(借-贷)
                      r1 numeric,--前年同期(借-贷)

                      m1 numeric,--年累(借-贷)
                      n1 numeric,--去年年累(借-贷)
                      o1 numeric,--前年年累(借-贷)

                      t1 numeric,  --年累借
                      u1 numeric,   --年累贷

                      lc_begin numeric, -- 期初金额
                      lc_debit numeric, -- 本期借方发生额
                      lc_credit numeric, -- 本期贷方发生额
                      lc_balance numeric, -- 期末借方金额

                      lc_a1 numeric,--当期(余额)
                      lc_b1 numeric,--前一期(余额)
                      lc_c1 numeric,--前二期(余额)
                      lc_d1 numeric,--去年同期(余额)
                      lc_p1 numeric,--前年同期(余额)

                      lc_e1 numeric,--年初余额
                      lc_f1 numeric,--去年年初余额
                      lc_s1 numeric,--前年年初余额

                      lc_g1 numeric,--年累余额
                      lc_h1 numeric,--去年年累余额
                      lc_q1 numeric,--前年年累余额

                      lc_i1 numeric,--当期(借-贷)
                      lc_j1 numeric,--前一期(借-贷)
                      lc_k1 numeric,--前二期(借-贷
                      lc_l1 numeric,--去年同期(借-贷)
                      lc_r1 numeric,--前年同期(借-贷)

                      lc_m1 numeric,--年累(借-贷)
                      lc_n1 numeric,--去年年累(借-贷)
                      lc_o1 numeric, --前年年累(借-贷)

                      lc_t1 numeric,  --年累借
                      lc_u1 numeric   --年累贷
                    );

                    create temp table account_balance_sum(
                      period_id integer, -- 期别
                      master_id integer, -- 总帐月结处理
                      account_id integer, -- 会计科目
                      sub_account_id integer, -- 辅助核算
                      sub_account_type character varying, -- 会科类型
                      parent_id integer, -- 上级科目
                      begin numeric, -- 期初金额(原币)
                      debit numeric, -- 本期借方(原币)
                      credit numeric, -- 本期贷方(原币)
                      balance numeric, -- 期末余额(原币)

                      a1 numeric,--当期(余额)
                      b1 numeric,--前一期(余额)
                      c1 numeric,--前二期(余额)
                      d1 numeric,--去年同期(余额)
                      p1 numeric,--前年同期(余额)

                      e1 numeric,--年初余额
                      f1 numeric,--去年年初余额
                      s1 numeric,--前年年初余额

                      g1 numeric,--年累余额
                      h1 numeric,--去年年累余额
                      q1 numeric,--前年年累余额

                      i1 numeric,--当期(借-贷)
                      j1 numeric,--前一期(借-贷)
                      k1 numeric,--前二期(借-贷
                      l1 numeric,--去年同期(借-贷)
                      r1 numeric,--前年同期(借-贷)

                      m1 numeric,--年累(借-贷)
                      n1 numeric,--去年年累(借-贷)
                      o1 numeric,--前年年累(借-贷)

                      t1 numeric,  --年累借
                      u1 numeric,   --年累贷

                      lc_begin numeric, -- 期初金额
                      lc_debit numeric, -- 本期借方发生额
                      lc_credit numeric, -- 本期贷方发生额
                      lc_balance numeric, -- 期末借方金额

                      lc_a1 numeric,--当期(余额)
                      lc_b1 numeric,--前一期(余额)
                      lc_c1 numeric,--前二期(余额)
                      lc_d1 numeric,--去年同期(余额)
                      lc_p1 numeric,--前年同期(余额)

                      lc_e1 numeric,--年初余额
                      lc_f1 numeric,--去年年初余额
                      lc_s1 numeric,--前年年初余额

                      lc_g1 numeric,--年累余额
                      lc_h1 numeric,--去年同期年累余额
                      lc_q1 numeric,--前年同期年累余额

                      lc_i1 numeric,--当期(借-贷)
                      lc_j1 numeric,--前一期(借-贷)
                      lc_k1 numeric,--前二期(借-贷
                      lc_l1 numeric,--去年同期(借-贷)
                      lc_r1 numeric,--前年同期(借-贷)

                      lc_m1 numeric,--年累(借-贷)
                      lc_n1 numeric,--去年同期年累(借-贷)
                      lc_o1 numeric,--前年同期年累(借-贷)

                      lc_t1 numeric,  --年累借
                      lc_u1 numeric,   --年累贷
                      level integer
                    );

                    --本期 借方 贷方
                    insert into account_balance_t(account_id,sub_account_id,sub_account_type,parent_id,
                                                  debit,credit,lc_debit,lc_credit)
                    select a.account_id,c.sub_account_id,e.sub_account_type,e.parent_id,
                           sum(coalesce(debit,0)) as debit,
                           sum(coalesce(credit,0)) as credit,
                           sum(coalesce(lc_debit,0)) as lc_debit,
                           sum(coalesce(lc_credit,0)) as lc_credit
                      from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                          left join sub_account_line c on a.id=c.voucher_line_id
                                          left join cncw_account e on a.account_id=e.id
                                          left join subaccount_category d on c.category_id=d.id
                     where 1=1
                       and b.date between from_date and to_date
                       and b.state='audited'
                       and (e.sub_account_type='has' and d.code<>'cash_flow')
                    group by a.account_id,c.sub_account_id,e.sub_account_type,e.parent_id;

                    insert into account_balance_t(account_id,sub_account_type,parent_id,
                                                  debit,credit,lc_debit,lc_credit)
                    select a.account_id,e.sub_account_type,e.parent_id,
                           sum(coalesce(debit,0)) as debit,
                           sum(coalesce(credit,0)) as credit,
                           sum(coalesce(lc_debit,0)) as lc_debit,
                           sum(coalesce(lc_credit,0)) as lc_credit
                      from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                          left join cncw_account e on a.account_id=e.id
                                          left join subaccount_category d on e.subaccount_category_id=d.id
                     where 1=1
                       and b.date between from_date and to_date
                       and b.state='audited'
                       and (e.sub_account_type='has' and d.code<>'cash_flow')
                    group by a.account_id,e.sub_account_type,e.parent_id;

                    --本期 借方 贷方
                    insert into account_balance_t(account_id,sub_account_type,parent_id,
                                                  debit,credit,lc_debit,lc_credit)
                    select a.account_id,e.sub_account_type,e.parent_id,
                           sum(coalesce(debit,0)) as debit,
                           sum(coalesce(credit,0)) as credit,
                           sum(coalesce(lc_debit,0)) as lc_debit,
                           sum(coalesce(lc_credit,0)) as lc_credit
                      from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                          left join cncw_account e on a.account_id=e.id
                                          left join subaccount_category d on e.subaccount_category_id=d.id
                     where 1=1
                       and b.date between from_date and to_date
                       and b.state='audited'
                       and e.sub_account_type ='none'
                    group by a.account_id,e.sub_account_type,e.parent_id;--a.sub_account_id,


                    --现金流 [科] 目
                    insert into account_balance_t(account_id,sub_account_type,parent_id,
                                                  debit,credit,lc_debit,lc_credit)
                    select a.account_id,e.sub_account_type,e.parent_id,
                           sum(coalesce(debit,0)) as debit,
                           sum(coalesce(credit,0)) as credit,
                           sum(coalesce(lc_debit,0)) as lc_debit,
                           sum(coalesce(lc_credit,0)) as lc_credit
                      from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                          left join cncw_account e on a.account_id=e.id
                                          left join subaccount_category d on e.subaccount_category_id=d.id
                     where 1=1
                       and b.date between from_date and to_date
                       and b.state='audited'
                       and (e.sub_account_type='has' and d.code='cash_flow')
                    group by a.account_id,e.sub_account_type,e.parent_id;--,a.sub_account_id

                    --现金流  [项]  目
                    insert into account_balance_t(sub_account_id,sub_account_type,
                                                  debit,credit,lc_debit,lc_credit)
                    select c.sub_account_id,e.sub_account_type,
                           sum(coalesce(debit,0)) as debit,
                           sum(coalesce(credit,0)) as credit,
                           sum(coalesce(lc_debit,0)) as lc_debit,
                           sum(coalesce(lc_credit,0)) as lc_credit
                      from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                          left join sub_account_line c on a.id=c.voucher_line_id
                                          left join cncw_account e on a.account_id=e.id
                                          left join subaccount_category d on e.subaccount_category_id=d.id
                     where 1=1
                       and b.date between from_date and to_date
                       and b.state='audited'
                       and (e.sub_account_type='has' and d.code='cash_flow')
                    group by c.sub_account_id,e.sub_account_type;

                    --取上期 期初金额 前一期(余额) 前二期(余额) 前一期(借-贷) 前二期(借-贷) 年累余额,年初余额,年累(借-贷)
                    --       期末余额 期末余额     前一期(余额) 当期(借-贷)   前一期(借-贷) 年累余额,年初余额,年累(借-贷)
                    insert into account_balance_t(account_id,sub_account_id,sub_account_type,parent_id,begin,b1,c1,j1,k1,g1,e1,m1,t1,u1
                                                  ,lc_begin,lc_b1,lc_c1,lc_j1,lc_k1,lc_g1,lc_e1,lc_m1,lc_t1,lc_u1)
                    select a.account_id,a.sub_account_id,a.sub_account_type,a.parent_id,balance,balance,begin,i1,j1

                           ,case when EXTRACT(MONTH from from_date)=1 then 0 else g1 end as g1 --1月份为0 否则为上月年累 后面再加上  本期余额
                           ,case when EXTRACT(MONTH from from_date)=1 then balance else e1 end as e1 --1月份为期末余额  否则为 年初余额
                           ,case when EXTRACT(MONTH from from_date)=1 then 0 else m1 end as m1 --1月份为0 否则为上月年累(借-贷) 后面再加上  本期(借-贷)
                           ,case when EXTRACT(MONTH from from_date)=1 then 0 else t1 end as t1
                           ,case when EXTRACT(MONTH from from_date)=1 then 0 else u1 end as u1
                           ,lc_balance,lc_balance,lc_begin,lc_i1,lc_j1
                           ,case when EXTRACT(MONTH from from_date)=1 then 0 else lc_g1 end as lc_g1
                           ,case when EXTRACT(MONTH from from_date)=1 then lc_balance else lc_e1 end as lc_e1
                           ,case when EXTRACT(MONTH from from_date)=1 then 0 else lc_m1 end as lc_m1
                           ,case when EXTRACT(MONTH from from_date)=1 then 0 else lc_t1 end as lc_t1
                           ,case when EXTRACT(MONTH from from_date)=1 then 0 else lc_t1 end as lc_u1
                      from account_balance  a --left join cncw_account b on a.account_id=b.id
                     where period_id=pre_period_id_t;
                       --and account_id is not null  要包含 现金流  [项]  目 ;
                       --and b.account_category not in ('5','6','7');--成本费用 月底结转
                     --SELECT EXTRACT(MONTH from TIMESTAMP '2001-02-16');

                    --取 去年初余额  年初余额=上年度12月份余额
                    insert into account_balance_t(account_id,sub_account_id,sub_account_type,parent_id,f1,lc_f1)
                    select account_id,sub_account_id,sub_account_type,parent_id,balance,lc_balance
                      from account_balance
                      where period_id in (select id from account_period where name=year_t::int-2 ||'12' limit 1);
                        --and account_id is not null;

                    --取 前年初余额  年初余额=上年度12月份余额
                    insert into account_balance_t(account_id,sub_account_id,sub_account_type,parent_id,s1,lc_s1)
                    select account_id,sub_account_id,sub_account_type,parent_id,balance,lc_balance
                      from account_balance
                     where period_id in (select id from account_period where name=year_t::int-3 ||'12' limit 1);
                       --and account_id is not null;


                    --去年同期 去年同期(余额)  去年同期年累余额  去年同期(借-贷)    去年同期年累(借-贷) 前年同期(余额)  前年同期年累余额  前年同期(借-贷)    前年同期年累(借-贷)
                    --         去年同期(余额)  年累余额          本期(借-贷)        年累(借-贷)         去年同期(余额)  去年同期年累余额  去年同期(借-贷)    去年同期年累(借-贷)
                    insert into account_balance_t(account_id,sub_account_id,sub_account_type,parent_id,d1,h1,l1,n1,p1,q1,r1,o1
                                                  ,lc_d1,lc_h1,lc_l1,lc_n1,lc_p1,lc_q1,lc_r1,lc_o1)
                    select account_id,sub_account_id,sub_account_type,parent_id,a1,g1,i1,m1,d1,h1,l1,n1
                                                  ,lc_a1,lc_g1,lc_i1,lc_m1,lc_d1,lc_h1,lc_l1,lc_n1
                      from account_balance
                     where period_id in (select id from account_period where name=year_t::int-1 ||substring(name,5,2) limit 1);
                       --and account_id is not null;

                    update account_balance_t a set dc_type=b.dc_type
                      from cncw_account b
                     where a.account_id=b.id;

                    insert into account_balance_sum (account_id,sub_account_id,sub_account_type,parent_id
                                         ,begin,debit,credit,i1,balance,a1
                                         ,b1,c1,j1,k1,g1,e1,f1,s1,d1,h1,l1,n1,m1,p1,q1,r1,o1,t1,u1

                                         ,lc_begin,lc_debit,lc_credit,lc_i1,lc_balance,lc_a1
                                         ,lc_b1,lc_c1,lc_j1,lc_k1,lc_g1,lc_e1,lc_f1,lc_s1,lc_d1,lc_h1,lc_l1,lc_n1,lc_m1,lc_p1,lc_q1,lc_r1,lc_o1,lc_t1,lc_u1
                                         )
                    select a.account_id,a.sub_account_id,a.sub_account_type,a.parent_id
                                   ,sum(coalesce(begin,0)) as begin
                                   ,sum(coalesce(debit,0)) as debit
                                   ,sum(coalesce(credit,0)) as credit
                                   ,sum(coalesce(debit,0)-coalesce(credit,0)) as i1
                                   ,sum(coalesce(begin,0))+(sum(coalesce(debit,0))-sum(coalesce(credit,0)))*(case when a.dc_type='D' then 1.0 else -1.0 end) as balance
                                   ,sum(coalesce(begin,0))+(sum(coalesce(debit,0))-sum(coalesce(credit,0)))*(case when a.dc_type='D' then 1.0 else -1.0 end) as a1
                                   ,sum(coalesce(b1,0)) as b1
                                   ,sum(coalesce(c1,0)) as c1
                                   ,sum(coalesce(j1,0)) as j1
                                   ,sum(coalesce(k1,0)) as k1
                                   ,sum(coalesce(g1,0)) as g1
                                   ,sum(coalesce(e1,0)) as e1
                                   ,sum(coalesce(f1,0)) as f1
                                   ,sum(coalesce(s1,0)) as s1
                                   ,sum(coalesce(d1,0)) as d1
                                   ,sum(coalesce(h1,0)) as h1
                                   ,sum(coalesce(l1,0)) as l1
                                   ,sum(coalesce(n1,0)) as n1
                                   ,sum(coalesce(m1,0)) as m1
                                   ,sum(coalesce(p1,0)) as p1
                                   ,sum(coalesce(q1,0)) as q1
                                   ,sum(coalesce(r1,0)) as r1
                                   ,sum(coalesce(o1,0)) as o1

                                   ,sum(coalesce(t1,0)) as t1
                                   ,sum(coalesce(u1,0)) as u1

                                   ,sum(coalesce(lc_begin,0)) as lc_begin
                                   ,sum(coalesce(lc_debit,0)) as lc_debit
                                   ,sum(coalesce(lc_credit,0)) as lc_credit
                                   ,sum(coalesce(lc_debit,0)-coalesce(lc_credit,0)) as lc_i1
                                   --,sum(coalesce(lc_begin,0)*(case when b.dc_type='D' then 1.0 else -1.0 end)+(coalesce(lc_debit,0)-coalesce(lc_credit,0))*(case when b.dc_type='D' then 1.0 else -1.0 end)) as lc_balance
                                   --,sum(coalesce(lc_begin,0)*(case when b.dc_type='D' then 1.0 else -1.0 end)+(coalesce(lc_debit,0)-coalesce(lc_credit,0))*(case when b.dc_type='D' then 1.0 else -1.0 end)) as lc_a1
                                   ,sum(coalesce(lc_begin,0))+(sum(coalesce(lc_debit,0))-sum(coalesce(lc_credit,0)))*(case when a.dc_type='D' then 1.0 else -1.0 end) as lc_balance
                                   ,sum(coalesce(lc_begin,0))+(sum(coalesce(lc_debit,0))-sum(coalesce(lc_credit,0)))*(case when a.dc_type='D' then 1.0 else -1.0 end) as lc_a1
                                   ,sum(coalesce(lc_b1,0)) as lc_b1
                                   ,sum(coalesce(lc_c1,0)) as lc_c1
                                   ,sum(coalesce(lc_j1,0)) as lc_j1
                                   ,sum(coalesce(lc_k1,0)) as lc_k1
                                   ,sum(coalesce(lc_g1,0)) as lc_g1
                                   ,sum(coalesce(lc_e1,0)) as lc_e1
                                   ,sum(coalesce(lc_f1,0)) as lc_f1
                                   ,sum(coalesce(lc_s1,0)) as lc_s1
                                   ,sum(coalesce(lc_d1,0)) as lc_d1
                                   ,sum(coalesce(lc_h1,0)) as lc_h1
                                   ,sum(coalesce(lc_l1,0)) as lc_l1
                                   ,sum(coalesce(lc_n1,0)) as lc_n1
                                   ,sum(coalesce(lc_m1,0)) as lc_m1
                                   ,sum(coalesce(lc_p1,0)) as lc_p1
                                   ,sum(coalesce(lc_q1,0)) as lc_q1
                                   ,sum(coalesce(lc_r1,0)) as lc_r1
                                   ,sum(coalesce(lc_o1,0)) as lc_o1
                                   ,sum(coalesce(lc_t1,0)) as lc_t1
                                   ,sum(coalesce(lc_u1,0)) as lc_u1
                     from account_balance_t a --left join cncw_account b on a.account_id=b.id
                    group by a.account_id,a.sub_account_id,a.sub_account_type,a.parent_id,a.dc_type;



                    for rec in select distinct coalesce(level,0) as level from cncw_account order by coalesce(level,0) desc
                    loop
                        update account_balance_sum a set begin=coalesce(b.begin,0)
                                  ,debit=coalesce(b.debit,0)
                                  ,credit=coalesce(b.credit,0)
                                  ,balance=coalesce(b.balance,0)
                                  ,a1=coalesce(b.a1,0)
                                  ,b1=coalesce(b.b1,0)
                                  ,c1=coalesce(b.c1,0)
                                  ,d1=coalesce(b.d1,0)
                                  ,e1=coalesce(b.e1,0)
                                  ,f1=coalesce(b.f1,0)
                                  ,g1=coalesce(b.g1,0)
                                  ,h1=coalesce(b.h1,0)
                                  ,i1=coalesce(b.i1,0)
                                  ,j1=coalesce(b.j1,0)
                                  ,k1=coalesce(b.k1,0)
                                  ,l1=coalesce(b.l1,0)
                                  ,m1=coalesce(b.m1,0)
                                  ,n1=coalesce(b.n1,0)
                                  ,o1=coalesce(b.o1,0)
                                  ,p1=coalesce(b.p1,0)
                                  ,q1=coalesce(b.q1,0)
                                  ,r1=coalesce(b.r1,0)
                                  ,s1=coalesce(b.s1,0)
                                  ,t1=coalesce(b.t1,0)
                                  ,u1=coalesce(b.u1,0)

                                  ,lc_begin=coalesce(b.lc_begin,0)
                                  ,lc_debit=coalesce(b.lc_debit,0)
                                  ,lc_credit=coalesce(b.lc_credit,0)
                                  ,lc_balance=coalesce(b.lc_balance,0)
                                  ,lc_a1=coalesce(b.lc_a1,0)
                                  ,lc_b1=coalesce(b.lc_b1,0)
                                  ,lc_c1=coalesce(b.lc_c1,0)
                                  ,lc_d1=coalesce(b.lc_d1,0)
                                  ,lc_e1=coalesce(b.lc_e1,0)
                                  ,lc_f1=coalesce(b.lc_f1,0)
                                  ,lc_g1=coalesce(b.lc_g1,0)
                                  ,lc_h1=coalesce(b.lc_h1,0)
                                  ,lc_i1=coalesce(b.lc_i1,0)
                                  ,lc_j1=coalesce(b.lc_j1,0)
                                  ,lc_k1=coalesce(b.lc_k1,0)
                                  ,lc_l1=coalesce(b.lc_l1,0)
                                  ,lc_m1=coalesce(b.lc_m1,0)
                                  ,lc_n1=coalesce(b.lc_n1,0)
                                  ,lc_o1=coalesce(b.lc_o1,0)
                                  ,lc_p1=coalesce(b.lc_p1,0)
                                  ,lc_q1=coalesce(b.lc_q1,0)
                                  ,lc_r1=coalesce(b.lc_r1,0)
                                  ,lc_s1=coalesce(b.lc_s1,0)
                                  ,lc_t1=coalesce(b.lc_t1,0)
                                  ,lc_u1=coalesce(b.lc_u1,0)

                         from (	select ee.parent_id
                                   ,sum(coalesce(begin,0)) as begin
                                   ,sum(coalesce(debit,0)) as debit
                                   ,sum(coalesce(credit,0)) as credit
                                   ,sum(coalesce(balance,0)) as balance
                                   ,sum(coalesce(a1,0)) as a1
                                   ,sum(coalesce(b1,0)) as b1
                                   ,sum(coalesce(c1,0)) as c1
                                   ,sum(coalesce(d1,0)) as d1
                                   ,sum(coalesce(e1,0)) as e1
                                   ,sum(coalesce(f1,0)) as f1
                                   ,sum(coalesce(g1,0)) as g1
                                   ,sum(coalesce(h1,0)) as h1
                                   ,sum(coalesce(i1,0)) as i1
                                   ,sum(coalesce(j1,0)) as j1
                                   ,sum(coalesce(k1,0)) as k1
                                   ,sum(coalesce(l1,0)) as l1
                                   ,sum(coalesce(m1,0)) as m1
                                   ,sum(coalesce(n1,0)) as n1
                                   ,sum(coalesce(o1,0)) as o1
                                   ,sum(coalesce(p1,0)) as p1
                                   ,sum(coalesce(q1,0)) as q1
                                   ,sum(coalesce(r1,0)) as r1
                                   ,sum(coalesce(s1,0)) as s1
                                   ,sum(coalesce(t1,0)) as t1
                                   ,sum(coalesce(u1,0)) as u1

                                   ,sum(coalesce(lc_begin,0)) as lc_begin
                                   ,sum(coalesce(lc_debit,0)) as lc_debit
                                   ,sum(coalesce(lc_credit,0)) as lc_credit
                                   ,sum(coalesce(lc_balance,0)) as lc_balance
                                   ,sum(coalesce(lc_a1,0)) as lc_a1
                                   ,sum(coalesce(lc_b1,0)) as lc_b1
                                   ,sum(coalesce(lc_c1,0)) as lc_c1
                                   ,sum(coalesce(lc_d1,0)) as lc_d1
                                   ,sum(coalesce(lc_e1,0)) as lc_e1
                                   ,sum(coalesce(lc_f1,0)) as lc_f1
                                   ,sum(coalesce(lc_g1,0)) as lc_g1
                                   ,sum(coalesce(lc_h1,0)) as lc_h1
                                   ,sum(coalesce(lc_i1,0)) as lc_i1
                                   ,sum(coalesce(lc_j1,0)) as lc_j1
                                   ,sum(coalesce(lc_k1,0)) as lc_k1
                                   ,sum(coalesce(lc_l1,0)) as lc_l1
                                   ,sum(coalesce(lc_m1,0)) as lc_m1
                                   ,sum(coalesce(lc_n1,0)) as lc_n1
                                   ,sum(coalesce(lc_o1,0)) as lc_o1
                                   ,sum(coalesce(lc_p1,0)) as lc_p1
                                   ,sum(coalesce(lc_q1,0)) as lc_q1
                                   ,sum(coalesce(lc_r1,0)) as lc_r1
                                   ,sum(coalesce(lc_s1,0)) as lc_s1
                                   ,sum(coalesce(lc_t1,0)) as lc_t1
                                   ,sum(coalesce(lc_u1,0)) as lc_u1
                              from account_balance_sum ee left join cncw_account gg on ee.parent_id=gg.id
                              where gg.level=rec.level
                             group by ee.parent_id ) b
                        where a.account_id=b.parent_id
                          and b.parent_id is not null;
                          --and a.level=rec.level-1;

                        RAISE NOTICE 'level: %', rec.level;

                    end loop;

                    update account_balance_sum set master_id=master_id_t,period_id=period_id_t;

                    delete from account_balance where period_id=period_id_t;

                    insert into account_balance (account_id,sub_account_id,sub_account_type,parent_id
                                         ,begin,debit,credit,balance
                                         ,a1,b1,c1,d1,e1,f1,g1,h1,i1,j1,k1,l1,m1,n1,o1,p1,q1,r1,s1,t1,u1

                                         ,lc_begin,lc_debit,lc_credit,lc_balance
                                         ,lc_a1,lc_b1,lc_c1,lc_d1,lc_e1,lc_f1,lc_g1,lc_h1,lc_i1,lc_j1,lc_k1,lc_l1,lc_m1,lc_n1,lc_o1,lc_p1,lc_q1,lc_r1,lc_s1,lc_t1,lc_u1

                                         ,master_id,period_id )
                    select account_id,sub_account_id,sub_account_type,parent_id
                                       ,sum(coalesce(begin,0)) as begin
                                       ,sum(coalesce(debit,0)) as debit
                                       ,sum(coalesce(credit,0)) as credit
                                       ,sum(coalesce(balance,0)) as balance
                                       ,sum(coalesce(a1,0)) as a1
                                       ,sum(coalesce(b1,0)) as b1
                                       ,sum(coalesce(c1,0)) as c1
                                       ,sum(coalesce(d1,0)) as d1
                                       ,sum(coalesce(e1,0)) as e1
                                       ,sum(coalesce(f1,0)) as f1
                                       ,sum(coalesce(g1,0)) as g1
                                       ,sum(coalesce(h1,0)) as h1
                                       ,sum(coalesce(i1,0)) as i1
                                       ,sum(coalesce(j1,0)) as j1
                                       ,sum(coalesce(k1,0)) as k1
                                       ,sum(coalesce(l1,0)) as l1
                                       ,sum(coalesce(m1,0)) as m1
                                       ,sum(coalesce(n1,0)) as n1
                                       ,sum(coalesce(o1,0)) as o1
                                       ,sum(coalesce(p1,0)) as p1
                                       ,sum(coalesce(q1,0)) as q1
                                       ,sum(coalesce(r1,0)) as r1
                                       ,sum(coalesce(s1,0)) as s1
                                       ,sum(coalesce(t1,0)) as t1
                                       ,sum(coalesce(u1,0)) as u1

                                       ,sum(coalesce(lc_begin,0)) as lc_begin
                                       ,sum(coalesce(lc_debit,0)) as lc_debit
                                       ,sum(coalesce(lc_credit,0)) as lc_credit
                                       ,sum(coalesce(lc_balance,0)) as lc_balance
                                       ,sum(coalesce(lc_a1,0)) as lc_a1
                                       ,sum(coalesce(lc_b1,0)) as lc_b1
                                       ,sum(coalesce(lc_c1,0)) as lc_c1
                                       ,sum(coalesce(lc_d1,0)) as lc_d1
                                       ,sum(coalesce(lc_e1,0)) as lc_e1
                                       ,sum(coalesce(lc_f1,0)) as lc_f1
                                       ,sum(coalesce(lc_g1,0)) as lc_g1
                                       ,sum(coalesce(lc_h1,0)) as lc_h1
                                       ,sum(coalesce(lc_i1,0)) as lc_i1
                                       ,sum(coalesce(lc_j1,0)) as lc_j1
                                       ,sum(coalesce(lc_k1,0)) as lc_k1
                                       ,sum(coalesce(lc_l1,0)) as lc_l1
                                       ,sum(coalesce(lc_m1,0)) as lc_m1
                                       ,sum(coalesce(lc_n1,0)) as lc_n1
                                       ,sum(coalesce(lc_o1,0)) as lc_o1
                                       ,sum(coalesce(lc_p1,0)) as lc_p1
                                       ,sum(coalesce(lc_q1,0)) as lc_q1
                                       ,sum(coalesce(lc_r1,0)) as lc_r1
                                       ,sum(coalesce(lc_s1,0)) as lc_s1
                                       ,sum(coalesce(lc_t1,0)) as lc_t1
                                       ,sum(coalesce(lc_u1,0)) as lc_u1
                                       ,master_id
                                       ,period_id
                     from account_balance_sum
                    group by account_id,sub_account_id,sub_account_type,parent_id,master_id,period_id ;

                    drop table account_balance_t;
                    drop table account_balance_sum;
                    --年累余额    =(上期)年累余额+本期余额
                    --年累(借-贷) =(上期)年累(借-贷)+本期(借-贷)
                    update account_balance set g1=coalesce(g1,0)+coalesce(balance,0)
                                              ,lc_g1=coalesce(lc_g1,0)+coalesce(lc_balance,0)
                                              ,m1=coalesce(m1,0)+coalesce(i1,0)
                                              ,lc_m1=coalesce(lc_m1,0)+coalesce(lc_i1,0)
                                              ,t1=coalesce(t1,0)+coalesce(debit,0)
                                              ,lc_t1=coalesce(lc_t1,0)+coalesce(lc_debit,0)
                                              ,u1=coalesce(u1,0)+coalesce(credit,0)
                                              ,lc_u1=coalesce(lc_u1,0)+coalesce(lc_credit,0)

                    where period_id=period_id_t;

                    update account_balance a set lc_begin_debit=coalesce(lc_begin,0),lc_balance_debit=coalesce(lc_balance,0)
                      from cncw_account b
                     where a.account_id=b.id
                       and b.dc_type='D'
                       and a.period_id=period_id_t;

                    update account_balance a set lc_begin_credit=coalesce(lc_begin,0),lc_balance_credit=coalesce(lc_balance,0)
                      from cncw_account b
                     where a.account_id=b.id
                       and b.dc_type='C'
                       and a.period_id=period_id_t;
                -- 更新 父级id
                    update account_balance a set balance_parent_id=b.id
                    from account_balance b
                    where b.sub_account_id is null
                        and a.sub_account_id is not null
                        and b.account_id=a.account_id
                        and b.period_id=a.period_id
                        and b.period_id=period_id_t;


                    --试算平衡
                    PERFORM account_trial_balance_settle(period_id_t,uid);
                    --三栏帐
                    PERFORM account_three_columns_settle(period_id_t,uid);
                 end;
                 $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

        """)

        self._cr.execute("""
        CREATE OR REPLACE FUNCTION public.account_balance_settle_unpass(
            period_id_t integer,
            pre_period_id_t integer,
            master_id_t integer,
            to_date timestamp without time zone,
            uid integer)
          RETURNS void AS
        $BODY$
                        -- DROP FUNCTION account_balance_settle(integer,integer,integer,timestamp ,integer)
                        -- select account_balance_settle(14,12,1,'2017-01-31',1)
                        --select *  from account_period order by id
                        -- drop table account_balance
                        DECLARE
                            from_date timestamp;
                            year_t character varying;   --年
                            rec record;
                        begin
                            select substring(name,4),date_start into year_t,from_date from account_period where id=period_id_t;

                            create temp table account_balance_t(
                              period_id integer, -- 期别
                              master_id integer, -- 总帐月结处理
                              account_id integer, -- 会计科目
                              dc_type  character varying, -- 借贷
                              sub_account_id integer, -- 辅助核算
                              sub_account_type character varying, -- 会科类型

                              parent_id integer, -- 上级科目
                                state  character varying, -- 状态
                              begin numeric, -- 期初金额(原币)
                              debit numeric, -- 本期借方(原币)
                              credit numeric, -- 本期贷方(原币)
                              balance numeric, -- 期末余额(原币)

                              a1 numeric,--当期(余额)
                              b1 numeric,--前一期(余额)
                              c1 numeric,--前二期(余额)
                              d1 numeric,--去年同期(余额)
                              p1 numeric,--前年同期(余额)

                              e1 numeric,--年初余额
                              f1 numeric,--去年年初余额
                              s1 numeric,--前年年初余额

                              g1 numeric,--年累余额
                              h1 numeric,--去年年累余额
                              q1 numeric,--前年年累余额

                              i1 numeric,--当期(借-贷)
                              j1 numeric,--前一期(借-贷)
                              k1 numeric,--前二期(借-贷
                              l1 numeric,--去年同期(借-贷)
                              r1 numeric,--前年同期(借-贷)

                              m1 numeric,--年累(借-贷)
                              n1 numeric,--去年年累(借-贷)
                              o1 numeric,--前年年累(借-贷)

                              t1 numeric,  --年累借
                              u1 numeric,   --年累贷

                              lc_begin numeric, -- 期初金额
                              lc_debit numeric, -- 本期借方发生额
                              lc_credit numeric, -- 本期贷方发生额
                              lc_balance numeric, -- 期末借方金额

                              lc_a1 numeric,--当期(余额)
                              lc_b1 numeric,--前一期(余额)
                              lc_c1 numeric,--前二期(余额)
                              lc_d1 numeric,--去年同期(余额)
                              lc_p1 numeric,--前年同期(余额)

                              lc_e1 numeric,--年初余额
                              lc_f1 numeric,--去年年初余额
                              lc_s1 numeric,--前年年初余额

                              lc_g1 numeric,--年累余额
                              lc_h1 numeric,--去年年累余额
                              lc_q1 numeric,--前年年累余额

                              lc_i1 numeric,--当期(借-贷)
                              lc_j1 numeric,--前一期(借-贷)
                              lc_k1 numeric,--前二期(借-贷
                              lc_l1 numeric,--去年同期(借-贷)
                              lc_r1 numeric,--前年同期(借-贷)

                              lc_m1 numeric,--年累(借-贷)
                              lc_n1 numeric,--去年年累(借-贷)
                              lc_o1 numeric, --前年年累(借-贷)

                              lc_t1 numeric,  --年累借
                              lc_u1 numeric   --年累贷
                            );

                            create temp table account_balance_sum(
                              period_id integer, -- 期别
                              master_id integer, -- 总帐月结处理
                              account_id integer, -- 会计科目
                              sub_account_id integer, -- 辅助核算
                              sub_account_type character varying, -- 会科类型

                              parent_id integer, -- 上级科目
                              state  character varying, -- 状态
                              begin numeric, -- 期初金额(原币)
                              debit numeric, -- 本期借方(原币)
                              credit numeric, -- 本期贷方(原币)
                              balance numeric, -- 期末余额(原币)

                              a1 numeric,--当期(余额)
                              b1 numeric,--前一期(余额)
                              c1 numeric,--前二期(余额)
                              d1 numeric,--去年同期(余额)
                              p1 numeric,--前年同期(余额)

                              e1 numeric,--年初余额
                              f1 numeric,--去年年初余额
                              s1 numeric,--前年年初余额

                              g1 numeric,--年累余额
                              h1 numeric,--去年年累余额
                              q1 numeric,--前年年累余额

                              i1 numeric,--当期(借-贷)
                              j1 numeric,--前一期(借-贷)
                              k1 numeric,--前二期(借-贷
                              l1 numeric,--去年同期(借-贷)
                              r1 numeric,--前年同期(借-贷)

                              m1 numeric,--年累(借-贷)
                              n1 numeric,--去年年累(借-贷)
                              o1 numeric,--前年年累(借-贷)

                              t1 numeric,  --年累借
                              u1 numeric,   --年累贷

                              lc_begin numeric, -- 期初金额
                              lc_debit numeric, -- 本期借方发生额
                              lc_credit numeric, -- 本期贷方发生额
                              lc_balance numeric, -- 期末借方金额

                              lc_a1 numeric,--当期(余额)
                              lc_b1 numeric,--前一期(余额)
                              lc_c1 numeric,--前二期(余额)
                              lc_d1 numeric,--去年同期(余额)
                              lc_p1 numeric,--前年同期(余额)

                              lc_e1 numeric,--年初余额
                              lc_f1 numeric,--去年年初余额
                              lc_s1 numeric,--前年年初余额

                              lc_g1 numeric,--年累余额
                              lc_h1 numeric,--去年同期年累余额
                              lc_q1 numeric,--前年同期年累余额

                              lc_i1 numeric,--当期(借-贷)
                              lc_j1 numeric,--前一期(借-贷)
                              lc_k1 numeric,--前二期(借-贷
                              lc_l1 numeric,--去年同期(借-贷)
                              lc_r1 numeric,--前年同期(借-贷)

                              lc_m1 numeric,--年累(借-贷)
                              lc_n1 numeric,--去年同期年累(借-贷)
                              lc_o1 numeric,--前年同期年累(借-贷)

                              lc_t1 numeric,  --年累借
                              lc_u1 numeric,   --年累贷
                              level integer
                            );

                            --本期 借方 贷方
                            insert into account_balance_t(account_id,sub_account_id,sub_account_type,parent_id,state,
                                                          debit,credit,lc_debit,lc_credit)
                            select a.account_id,c.sub_account_id,e.sub_account_type,e.parent_id,
                                                      (Case when  b.state='audited' then 'audited' else 'draft' end)as state,
                                   sum(coalesce(debit,0)) as debit,
                                   sum(coalesce(credit,0)) as credit,
                                   sum(coalesce(lc_debit,0)) as lc_debit,
                                   sum(coalesce(lc_credit,0)) as lc_credit
                              from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                                  left join sub_account_line c on a.id=c.voucher_line_id
                                                  left join cncw_account e on a.account_id=e.id
                                                  left join subaccount_category d on c.category_id=d.id
                             where 1=1
                               and b.date between from_date and to_date
                               and b.state!='cancel' -- 凭证已审核未审核都要出现 
                               and (e.sub_account_type='has' and d.code<>'cash_flow')
                            group by a.account_id,c.sub_account_id,e.sub_account_type,e.parent_id, b.state ;

                            insert into account_balance_t(account_id,sub_account_type,parent_id,state,debit,credit,lc_debit,lc_credit)
                            select a.account_id,e.sub_account_type,e.parent_id,
                                                           (Case when  b.state='audited' then 'audited' else 'draft' end)as state,
                                   sum(coalesce(debit,0)) as debit,
                                   sum(coalesce(credit,0)) as credit,
                                   sum(coalesce(lc_debit,0)) as lc_debit,
                                   sum(coalesce(lc_credit,0)) as lc_credit
                              from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                                  left join cncw_account e on a.account_id=e.id
                                                  left join subaccount_category d on e.subaccount_category_id=d.id
                             where 1=1
                               and b.date between from_date and to_date
                               and b.state!='cancel' -- 凭证已审核未审核都要出现 
                               and (e.sub_account_type='has' and d.code<>'cash_flow')
                            group by a.account_id,e.sub_account_type,e.parent_id, b.state;

                            --本期 借方 贷方
                            insert into account_balance_t(account_id,sub_account_type,parent_id,state,debit,credit,lc_debit,lc_credit)
                            select a.account_id,e.sub_account_type,e.parent_id,
                                                                           (Case when  b.state='audited' then 'audited' else 'draft' end)as state,
                                   sum(coalesce(debit,0)) as debit,
                                   sum(coalesce(credit,0)) as credit,
                                   sum(coalesce(lc_debit,0)) as lc_debit,
                                   sum(coalesce(lc_credit,0)) as lc_credit
                              from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                                  left join cncw_account e on a.account_id=e.id
                                                  left join subaccount_category d on e.subaccount_category_id=d.id
                             where 1=1
                               and b.date between from_date and to_date
                               and b.state!='cancel' -- 凭证已审核未审核都要出现 
                               and e.sub_account_type ='none'
                            group by a.account_id,e.sub_account_type,e.parent_id, b.state;--a.sub_account_id,


                            --现金流 [科] 目
                            insert into account_balance_t(account_id,sub_account_type,parent_id,state,debit,credit,lc_debit,lc_credit)
                            select a.account_id,e.sub_account_type,e.parent_id,
                                                                            (Case when  b.state='audited' then 'audited' else 'draft' end)as state,
                                   sum(coalesce(debit,0)) as debit,
                                   sum(coalesce(credit,0)) as credit,
                                   sum(coalesce(lc_debit,0)) as lc_debit,
                                   sum(coalesce(lc_credit,0)) as lc_credit
                              from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                                  left join cncw_account e on a.account_id=e.id
                                                  left join subaccount_category d on e.subaccount_category_id=d.id
                             where 1=1
                               and b.date between from_date and to_date
                                 and b.state!='cancel'  -- 凭证已审核未审核都要出现 
                               and (e.sub_account_type='has' and d.code='cash_flow')
                            group by a.account_id,e.sub_account_type,e.parent_id, b.state;--,a.sub_account_id

                            --现金流  [项]  目
                            insert into account_balance_t(sub_account_id,sub_account_type,state,debit,credit,lc_debit,lc_credit)
                            select c.sub_account_id,e.sub_account_type,
                               (Case when  b.state='audited' then 'audited' else 'draft' end)as state,
                                   sum(coalesce(debit,0)) as debit,
                                   sum(coalesce(credit,0)) as credit,
                                   sum(coalesce(lc_debit,0)) as lc_debit,
                                   sum(coalesce(lc_credit,0)) as lc_credit
                              from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                                  left join sub_account_line c on a.id=c.voucher_line_id
                                                  left join cncw_account e on a.account_id=e.id
                                                  left join subaccount_category d on e.subaccount_category_id=d.id
                             where 1=1
                               and b.date between from_date and to_date
                                and b.state!='cancel' -- 凭证已审核未审核都要出现 
                               and (e.sub_account_type='has' and d.code='cash_flow')
                            group by c.sub_account_id,e.sub_account_type, b.state;

                            --取上期 期初金额 前一期(余额) 前二期(余额) 前一期(借-贷) 前二期(借-贷) 年累余额,年初余额,年累(借-贷)
                            --       期末余额 期末余额     前一期(余额) 当期(借-贷)   前一期(借-贷) 年累余额,年初余额,年累(借-贷)
                            insert into account_balance_t(account_id,sub_account_id,sub_account_type,parent_id,begin,b1,c1,j1,k1,g1,e1,m1,t1,u1
                                                          ,lc_begin,lc_b1,lc_c1,lc_j1,lc_k1,lc_g1,lc_e1,lc_m1,lc_t1,lc_u1)
                            select a.account_id,a.sub_account_id,a.sub_account_type,a.parent_id,balance,balance,begin,i1,j1

                                   ,case when EXTRACT(MONTH from from_date)=1 then 0 else g1 end as g1 --1月份为0 否则为上月年累 后面再加上  本期余额
                                   ,case when EXTRACT(MONTH from from_date)=1 then balance else e1 end as e1 --1月份为期末余额  否则为 年初余额
                                   ,case when EXTRACT(MONTH from from_date)=1 then 0 else m1 end as m1 --1月份为0 否则为上月年累(借-贷) 后面再加上  本期(借-贷)
                                   ,case when EXTRACT(MONTH from from_date)=1 then 0 else t1 end as t1
                                   ,case when EXTRACT(MONTH from from_date)=1 then 0 else u1 end as u1
                                   ,lc_balance,lc_balance,lc_begin,lc_i1,lc_j1
                                   ,case when EXTRACT(MONTH from from_date)=1 then 0 else lc_g1 end as lc_g1
                                   ,case when EXTRACT(MONTH from from_date)=1 then lc_balance else lc_e1 end as lc_e1
                                   ,case when EXTRACT(MONTH from from_date)=1 then 0 else lc_m1 end as lc_m1
                                   ,case when EXTRACT(MONTH from from_date)=1 then 0 else lc_t1 end as lc_t1
                                   ,case when EXTRACT(MONTH from from_date)=1 then 0 else lc_t1 end as lc_u1
                              from account_balance  a --left join cncw_account b on a.account_id=b.id
                             where period_id=pre_period_id_t;
                               --and account_id is not null  要包含 现金流  [项]  目 ;
                               --and b.account_category not in ('5','6','7');--成本费用 月底结转
                             --SELECT EXTRACT(MONTH from TIMESTAMP '2001-02-16');

                            --取 去年初余额  年初余额=上年度12月份余额
                            insert into account_balance_t(account_id,sub_account_id,sub_account_type,parent_id,f1,lc_f1)
                            select account_id,sub_account_id,sub_account_type,parent_id,balance,lc_balance
                              from account_balance
                              where period_id in (select id from account_period where name=year_t::int-2 ||'12' limit 1);
                                --and account_id is not null;

                            --取 前年初余额  年初余额=上年度12月份余额
                            insert into account_balance_t(account_id,sub_account_id,sub_account_type,parent_id,s1,lc_s1)
                            select account_id,sub_account_id,sub_account_type,parent_id,balance,lc_balance
                              from account_balance
                             where period_id in (select id from account_period where name=year_t::int-3 ||'12' limit 1);
                               --and account_id is not null;


                            --去年同期 去年同期(余额)  去年同期年累余额  去年同期(借-贷)    去年同期年累(借-贷) 前年同期(余额)  前年同期年累余额  前年同期(借-贷)    前年同期年累(借-贷)
                            --         去年同期(余额)  年累余额          本期(借-贷)        年累(借-贷)         去年同期(余额)  去年同期年累余额  去年同期(借-贷)    去年同期年累(借-贷)
                            insert into account_balance_t(account_id,sub_account_id,sub_account_type,parent_id,d1,h1,l1,n1,p1,q1,r1,o1
                                                          ,lc_d1,lc_h1,lc_l1,lc_n1,lc_p1,lc_q1,lc_r1,lc_o1)
                            select account_id,sub_account_id,sub_account_type,parent_id,a1,g1,i1,m1,d1,h1,l1,n1
                                                          ,lc_a1,lc_g1,lc_i1,lc_m1,lc_d1,lc_h1,lc_l1,lc_n1
                              from account_balance
                             where period_id in (select id from account_period where name=year_t::int-1 ||substring(name,5,2) limit 1);
                               --and account_id is not null;

                            update account_balance_t a set dc_type=b.dc_type
                              from cncw_account b
                             where a.account_id=b.id;

                            insert into account_balance_sum (account_id,sub_account_id,sub_account_type,parent_id,state
                                                 ,begin,debit,credit,i1,balance,a1
                                                 ,b1,c1,j1,k1,g1,e1,f1,s1,d1,h1,l1,n1,m1,p1,q1,r1,o1,t1,u1

                                                 ,lc_begin,lc_debit,lc_credit,lc_i1,lc_balance,lc_a1
                                                 ,lc_b1,lc_c1,lc_j1,lc_k1,lc_g1,lc_e1,lc_f1,lc_s1,lc_d1,lc_h1,lc_l1,lc_n1,lc_m1,lc_p1,lc_q1,lc_r1,lc_o1,lc_t1,lc_u1
                                                 )
                            select a.account_id,a.sub_account_id,a.sub_account_type,a.parent_id,
                                            a.state as state
                                           ,sum(coalesce(begin,0)) as begin
                                           ,sum(coalesce(debit,0)) as debit
                                           ,sum(coalesce(credit,0)) as credit
                                           ,sum(coalesce(debit,0)-coalesce(credit,0)) as i1
                                           ,sum(coalesce(begin,0))+(sum(coalesce(debit,0))-sum(coalesce(credit,0)))*(case when a.dc_type='D' then 1.0 else -1.0 end) as balance
                                           ,sum(coalesce(begin,0))+(sum(coalesce(debit,0))-sum(coalesce(credit,0)))*(case when a.dc_type='D' then 1.0 else -1.0 end) as a1
                                           ,sum(coalesce(b1,0)) as b1
                                           ,sum(coalesce(c1,0)) as c1
                                           ,sum(coalesce(j1,0)) as j1
                                           ,sum(coalesce(k1,0)) as k1
                                           ,sum(coalesce(g1,0)) as g1
                                           ,sum(coalesce(e1,0)) as e1
                                           ,sum(coalesce(f1,0)) as f1
                                           ,sum(coalesce(s1,0)) as s1
                                           ,sum(coalesce(d1,0)) as d1
                                           ,sum(coalesce(h1,0)) as h1
                                           ,sum(coalesce(l1,0)) as l1
                                           ,sum(coalesce(n1,0)) as n1
                                           ,sum(coalesce(m1,0)) as m1
                                           ,sum(coalesce(p1,0)) as p1
                                           ,sum(coalesce(q1,0)) as q1
                                           ,sum(coalesce(r1,0)) as r1
                                           ,sum(coalesce(o1,0)) as o1

                                           ,sum(coalesce(t1,0)) as t1
                                           ,sum(coalesce(u1,0)) as u1

                                           ,sum(coalesce(lc_begin,0)) as lc_begin
                                           ,sum(coalesce(lc_debit,0)) as lc_debit
                                           ,sum(coalesce(lc_credit,0)) as lc_credit
                                           ,sum(coalesce(lc_debit,0)-coalesce(lc_credit,0)) as lc_i1
                                           --,sum(coalesce(lc_begin,0)*(case when b.dc_type='D' then 1.0 else -1.0 end)+(coalesce(lc_debit,0)-coalesce(lc_credit,0))*(case when b.dc_type='D' then 1.0 else -1.0 end)) as lc_balance
                                           --,sum(coalesce(lc_begin,0)*(case when b.dc_type='D' then 1.0 else -1.0 end)+(coalesce(lc_debit,0)-coalesce(lc_credit,0))*(case when b.dc_type='D' then 1.0 else -1.0 end)) as lc_a1
                                           ,sum(coalesce(lc_begin,0))+(sum(coalesce(lc_debit,0))-sum(coalesce(lc_credit,0)))*(case when a.dc_type='D' then 1.0 else -1.0 end) as lc_balance
                                           ,sum(coalesce(lc_begin,0))+(sum(coalesce(lc_debit,0))-sum(coalesce(lc_credit,0)))*(case when a.dc_type='D' then 1.0 else -1.0 end) as lc_a1
                                           ,sum(coalesce(lc_b1,0)) as lc_b1
                                           ,sum(coalesce(lc_c1,0)) as lc_c1
                                           ,sum(coalesce(lc_j1,0)) as lc_j1
                                           ,sum(coalesce(lc_k1,0)) as lc_k1
                                           ,sum(coalesce(lc_g1,0)) as lc_g1
                                           ,sum(coalesce(lc_e1,0)) as lc_e1
                                           ,sum(coalesce(lc_f1,0)) as lc_f1
                                           ,sum(coalesce(lc_s1,0)) as lc_s1
                                           ,sum(coalesce(lc_d1,0)) as lc_d1
                                           ,sum(coalesce(lc_h1,0)) as lc_h1
                                           ,sum(coalesce(lc_l1,0)) as lc_l1
                                           ,sum(coalesce(lc_n1,0)) as lc_n1
                                           ,sum(coalesce(lc_m1,0)) as lc_m1
                                           ,sum(coalesce(lc_p1,0)) as lc_p1
                                           ,sum(coalesce(lc_q1,0)) as lc_q1
                                           ,sum(coalesce(lc_r1,0)) as lc_r1
                                           ,sum(coalesce(lc_o1,0)) as lc_o1
                                           ,sum(coalesce(lc_t1,0)) as lc_t1
                                           ,sum(coalesce(lc_u1,0)) as lc_u1
                             from account_balance_t a --left join cncw_account b on a.account_id=b.id
                            group by a.account_id,a.sub_account_id,a.sub_account_type,a.parent_id,a.dc_type,a.state;



                            for rec in select distinct coalesce(level,0) as level from cncw_account order by coalesce(level,0) desc
                            loop
                                update account_balance_sum a set begin=coalesce(b.begin,0)
                                          ,debit=coalesce(b.debit,0)
                                          ,credit=coalesce(b.credit,0)
                                          ,balance=coalesce(b.balance,0)
                                          ,a1=coalesce(b.a1,0)
                                          ,b1=coalesce(b.b1,0)
                                          ,c1=coalesce(b.c1,0)
                                          ,d1=coalesce(b.d1,0)
                                          ,e1=coalesce(b.e1,0)
                                          ,f1=coalesce(b.f1,0)
                                          ,g1=coalesce(b.g1,0)
                                          ,h1=coalesce(b.h1,0)
                                          ,i1=coalesce(b.i1,0)
                                          ,j1=coalesce(b.j1,0)
                                          ,k1=coalesce(b.k1,0)
                                          ,l1=coalesce(b.l1,0)
                                          ,m1=coalesce(b.m1,0)
                                          ,n1=coalesce(b.n1,0)
                                          ,o1=coalesce(b.o1,0)
                                          ,p1=coalesce(b.p1,0)
                                          ,q1=coalesce(b.q1,0)
                                          ,r1=coalesce(b.r1,0)
                                          ,s1=coalesce(b.s1,0)
                                          ,t1=coalesce(b.t1,0)
                                          ,u1=coalesce(b.u1,0)

                                          ,lc_begin=coalesce(b.lc_begin,0)
                                          ,lc_debit=coalesce(b.lc_debit,0)
                                          ,lc_credit=coalesce(b.lc_credit,0)
                                          ,lc_balance=coalesce(b.lc_balance,0)
                                          ,lc_a1=coalesce(b.lc_a1,0)
                                          ,lc_b1=coalesce(b.lc_b1,0)
                                          ,lc_c1=coalesce(b.lc_c1,0)
                                          ,lc_d1=coalesce(b.lc_d1,0)
                                          ,lc_e1=coalesce(b.lc_e1,0)
                                          ,lc_f1=coalesce(b.lc_f1,0)
                                          ,lc_g1=coalesce(b.lc_g1,0)
                                          ,lc_h1=coalesce(b.lc_h1,0)
                                          ,lc_i1=coalesce(b.lc_i1,0)
                                          ,lc_j1=coalesce(b.lc_j1,0)
                                          ,lc_k1=coalesce(b.lc_k1,0)
                                          ,lc_l1=coalesce(b.lc_l1,0)
                                          ,lc_m1=coalesce(b.lc_m1,0)
                                          ,lc_n1=coalesce(b.lc_n1,0)
                                          ,lc_o1=coalesce(b.lc_o1,0)
                                          ,lc_p1=coalesce(b.lc_p1,0)
                                          ,lc_q1=coalesce(b.lc_q1,0)
                                          ,lc_r1=coalesce(b.lc_r1,0)
                                          ,lc_s1=coalesce(b.lc_s1,0)
                                          ,lc_t1=coalesce(b.lc_t1,0)
                                          ,lc_u1=coalesce(b.lc_u1,0)

                                 from (	select ee.parent_id
                                           ,sum(coalesce(begin,0)) as begin
                                           ,sum(coalesce(debit,0)) as debit
                                           ,sum(coalesce(credit,0)) as credit
                                           ,sum(coalesce(balance,0)) as balance
                                           ,sum(coalesce(a1,0)) as a1
                                           ,sum(coalesce(b1,0)) as b1
                                           ,sum(coalesce(c1,0)) as c1
                                           ,sum(coalesce(d1,0)) as d1
                                           ,sum(coalesce(e1,0)) as e1
                                           ,sum(coalesce(f1,0)) as f1
                                           ,sum(coalesce(g1,0)) as g1
                                           ,sum(coalesce(h1,0)) as h1
                                           ,sum(coalesce(i1,0)) as i1
                                           ,sum(coalesce(j1,0)) as j1
                                           ,sum(coalesce(k1,0)) as k1
                                           ,sum(coalesce(l1,0)) as l1
                                           ,sum(coalesce(m1,0)) as m1
                                           ,sum(coalesce(n1,0)) as n1
                                           ,sum(coalesce(o1,0)) as o1
                                           ,sum(coalesce(p1,0)) as p1
                                           ,sum(coalesce(q1,0)) as q1
                                           ,sum(coalesce(r1,0)) as r1
                                           ,sum(coalesce(s1,0)) as s1
                                           ,sum(coalesce(t1,0)) as t1
                                           ,sum(coalesce(u1,0)) as u1

                                           ,sum(coalesce(lc_begin,0)) as lc_begin
                                           ,sum(coalesce(lc_debit,0)) as lc_debit
                                           ,sum(coalesce(lc_credit,0)) as lc_credit
                                           ,sum(coalesce(lc_balance,0)) as lc_balance
                                           ,sum(coalesce(lc_a1,0)) as lc_a1
                                           ,sum(coalesce(lc_b1,0)) as lc_b1
                                           ,sum(coalesce(lc_c1,0)) as lc_c1
                                           ,sum(coalesce(lc_d1,0)) as lc_d1
                                           ,sum(coalesce(lc_e1,0)) as lc_e1
                                           ,sum(coalesce(lc_f1,0)) as lc_f1
                                           ,sum(coalesce(lc_g1,0)) as lc_g1
                                           ,sum(coalesce(lc_h1,0)) as lc_h1
                                           ,sum(coalesce(lc_i1,0)) as lc_i1
                                           ,sum(coalesce(lc_j1,0)) as lc_j1
                                           ,sum(coalesce(lc_k1,0)) as lc_k1
                                           ,sum(coalesce(lc_l1,0)) as lc_l1
                                           ,sum(coalesce(lc_m1,0)) as lc_m1
                                           ,sum(coalesce(lc_n1,0)) as lc_n1
                                           ,sum(coalesce(lc_o1,0)) as lc_o1
                                           ,sum(coalesce(lc_p1,0)) as lc_p1
                                           ,sum(coalesce(lc_q1,0)) as lc_q1
                                           ,sum(coalesce(lc_r1,0)) as lc_r1
                                           ,sum(coalesce(lc_s1,0)) as lc_s1
                                           ,sum(coalesce(lc_t1,0)) as lc_t1
                                           ,sum(coalesce(lc_u1,0)) as lc_u1
                                      from account_balance_sum ee left join cncw_account gg on ee.parent_id=gg.id
                                      where gg.level=rec.level
                                     group by ee.parent_id ) b
                                where a.account_id=b.parent_id
                                  and b.parent_id is not null;
                                  --and a.level=rec.level-1;

                                RAISE NOTICE 'level: %', rec.level;

                            end loop;

                            update account_balance_sum set master_id=master_id_t,period_id=period_id_t;

                            delete from account_balance where period_id=period_id_t;

                            insert into account_balance (account_id,sub_account_id,sub_account_type,parent_id,state
                                                 ,begin,debit,credit,balance
                                                 ,a1,b1,c1,d1,e1,f1,g1,h1,i1,j1,k1,l1,m1,n1,o1,p1,q1,r1,s1,t1,u1

                                                 ,lc_begin,lc_debit,lc_credit,lc_balance
                                                 ,lc_a1,lc_b1,lc_c1,lc_d1,lc_e1,lc_f1,lc_g1,lc_h1,lc_i1,lc_j1,lc_k1,lc_l1,lc_m1,lc_n1,lc_o1,lc_p1,lc_q1,lc_r1,lc_s1,lc_t1,lc_u1

                                                 ,master_id,period_id )
                            select account_id,sub_account_id,sub_account_type,parent_id,
                                                state
                                               ,sum(coalesce(begin,0)) as begin
                                               ,sum(coalesce(debit,0)) as debit
                                               ,sum(coalesce(credit,0)) as credit
                                               ,sum(coalesce(balance,0)) as balance
                                               ,sum(coalesce(a1,0)) as a1
                                               ,sum(coalesce(b1,0)) as b1
                                               ,sum(coalesce(c1,0)) as c1
                                               ,sum(coalesce(d1,0)) as d1
                                               ,sum(coalesce(e1,0)) as e1
                                               ,sum(coalesce(f1,0)) as f1
                                               ,sum(coalesce(g1,0)) as g1
                                               ,sum(coalesce(h1,0)) as h1
                                               ,sum(coalesce(i1,0)) as i1
                                               ,sum(coalesce(j1,0)) as j1
                                               ,sum(coalesce(k1,0)) as k1
                                               ,sum(coalesce(l1,0)) as l1
                                               ,sum(coalesce(m1,0)) as m1
                                               ,sum(coalesce(n1,0)) as n1
                                               ,sum(coalesce(o1,0)) as o1
                                               ,sum(coalesce(p1,0)) as p1
                                               ,sum(coalesce(q1,0)) as q1
                                               ,sum(coalesce(r1,0)) as r1
                                               ,sum(coalesce(s1,0)) as s1
                                               ,sum(coalesce(t1,0)) as t1
                                               ,sum(coalesce(u1,0)) as u1

                                               ,sum(coalesce(lc_begin,0)) as lc_begin
                                               ,sum(coalesce(lc_debit,0)) as lc_debit
                                               ,sum(coalesce(lc_credit,0)) as lc_credit
                                               ,sum(coalesce(lc_balance,0)) as lc_balance
                                               ,sum(coalesce(lc_a1,0)) as lc_a1
                                               ,sum(coalesce(lc_b1,0)) as lc_b1
                                               ,sum(coalesce(lc_c1,0)) as lc_c1
                                               ,sum(coalesce(lc_d1,0)) as lc_d1
                                               ,sum(coalesce(lc_e1,0)) as lc_e1
                                               ,sum(coalesce(lc_f1,0)) as lc_f1
                                               ,sum(coalesce(lc_g1,0)) as lc_g1
                                               ,sum(coalesce(lc_h1,0)) as lc_h1
                                               ,sum(coalesce(lc_i1,0)) as lc_i1
                                               ,sum(coalesce(lc_j1,0)) as lc_j1
                                               ,sum(coalesce(lc_k1,0)) as lc_k1
                                               ,sum(coalesce(lc_l1,0)) as lc_l1
                                               ,sum(coalesce(lc_m1,0)) as lc_m1
                                               ,sum(coalesce(lc_n1,0)) as lc_n1
                                               ,sum(coalesce(lc_o1,0)) as lc_o1
                                               ,sum(coalesce(lc_p1,0)) as lc_p1
                                               ,sum(coalesce(lc_q1,0)) as lc_q1
                                               ,sum(coalesce(lc_r1,0)) as lc_r1
                                               ,sum(coalesce(lc_s1,0)) as lc_s1
                                               ,sum(coalesce(lc_t1,0)) as lc_t1
                                               ,sum(coalesce(lc_u1,0)) as lc_u1
                                               ,master_id
                                               ,period_id
                             from account_balance_sum
                            group by account_id,sub_account_id,sub_account_type,parent_id,master_id,period_id,state ;

                            drop table account_balance_t;
                            drop table account_balance_sum;
                            --年累余额    =(上期)年累余额+本期余额
                            --年累(借-贷) =(上期)年累(借-贷)+本期(借-贷)
                            update account_balance set g1=coalesce(g1,0)+coalesce(balance,0)
                                                      ,lc_g1=coalesce(lc_g1,0)+coalesce(lc_balance,0)
                                                      ,m1=coalesce(m1,0)+coalesce(i1,0)
                                                      ,lc_m1=coalesce(lc_m1,0)+coalesce(lc_i1,0)
                                                      ,t1=coalesce(t1,0)+coalesce(debit,0)
                                                      ,lc_t1=coalesce(lc_t1,0)+coalesce(lc_debit,0)
                                                      ,u1=coalesce(u1,0)+coalesce(credit,0)
                                                      ,lc_u1=coalesce(lc_u1,0)+coalesce(lc_credit,0)

                            where period_id=period_id_t;

                            update account_balance a set lc_begin_debit=coalesce(lc_begin,0),lc_balance_debit=coalesce(lc_balance,0)
                              from cncw_account b
                             where a.account_id=b.id
                               and b.dc_type='D'
                               and a.period_id=period_id_t;

                            update account_balance a set lc_begin_credit=coalesce(lc_begin,0),lc_balance_credit=coalesce(lc_balance,0)
                              from cncw_account b
                             where a.account_id=b.id
                               and b.dc_type='C'
                               and a.period_id=period_id_t;
                        -- 更新 父级id
                            update account_balance a set balance_parent_id=b.id
                            from account_balance b
                            where b.sub_account_id is null
                                and a.sub_account_id is not null
                                and b.account_id=a.account_id
                                and b.period_id=a.period_id
                                and b.period_id=period_id_t;


                            --试算平衡
                            PERFORM account_trial_balance_settle(period_id_t,uid);
                            --三栏帐
                            PERFORM account_three_columns_settle(period_id_t,uid);
                         end;
                         $BODY$
          LANGUAGE plpgsql VOLATILE
          COST 100;

                """)



    def check_data(self):
        if self.result_ids:
            self.result_ids.unlink()
        date_start = self.period_id.date_start
        date_start_to_datetime = date_start.strftime(DTF)
        date_stop = self.to_date or self.period_id.date_stop
        date_stop_to_datetime = (date_stop + datetime.timedelta(days=1)).strftime(DTF)

        message = ''
        val_list = []
        for line_id in self.rule_id.line_ids:
            domain = [
                (line_id.field_id.name, '>=', date_start_to_datetime),
                (line_id.field_id.name, '<', date_stop_to_datetime)
            ]
            rule_domain = eval(line_id.rule_domain)
            domain += rule_domain
            res = self.env[line_id.model_id.model].search(domain)
            if res:
                tmp_message = line_id.note
                for result in res:
                    val = {'name': line_id.note,
                           'master_id': self.id,
                           'model_id': '%s,%s' % (result._name, result.id)}
                    val_list.append(val)
                    tmp_message += result.name_get()[0][1] + ','
                tmp_message += '\n'
                message += tmp_message
        self.env['account.balance.settle.line'].create(val_list)
        self._cr.commit()
        if message != '':
            # raise UserError(message)
            return message
        else:
            return


class AccountBalanceSettleRule(models.Model):
    _name = 'account.balance.settle.rule'

    name = fields.Char(String=u'名称')
    line_ids = fields.One2many('account.balance.settle.rule.line', 'order_id', string=u'规则明细')


class AccountBalanceSettleRuleLine(models.Model):
    _name = 'account.balance.settle.rule.line'

    order_id = fields.Many2one('account.balance.settle.rule', ondelete='cascade')
    model_id = fields.Many2one('ir.model', string=u'模型')
    field_id = fields.Many2one('ir.model.fields', string=u'字段')
    rule_domain = fields.Text(string=u'未处理判定规则')
    note = fields.Text(string=u'提示信息')


class AccountBalanceLine(models.Model):
    _name = 'account.balance.settle.line'
    _description = '总帐检查结果明细'

    master_id = fields.Many2one('account.balance.settle')
    name = fields.Char('说明')
    model_id = fields.Reference(string='单据', selection='_selection_target_model')

    @api.model
    def _selection_target_model(self):
        models = self.env['ir.model'].search([])
        return [(model.model, model.name) for model in models]


class account_balance(models.Model):
    _name = 'account.balance'
    _description = u'科目余额表'
    _rec_name = 'account_id'

    master_id = fields.Many2one('account.balance.settle', u'总账月结处理', ondelete="cascade")
    period_id = fields.Many2one('account.period', u'期别')
    account_id = fields.Many2one('cncw.account', u'会计科目', ondelete="restrict")

    dc_type = fields.Selection(base_cw.public.DC_TYPE, u'借贷', related='account_id.dc_type', readonly=True, )
    sub_account_id = fields.Many2one('res.partner', u'辅助核算', ondelete="restrict")
    parent_id = fields.Many2one('cncw.account', u'上级科目', ondelete='restrict')
    sub_account_type = fields.Selection(base_cw.public.SUB_ACCOUNT_TYPE, u'会科类型')
    state = fields.Selection([('draft', u'未审核'),
                              ('audited', u'已审核'), ], u'状态')
    balance_child_ids = fields.One2many('account.balance', 'balance_parent_id', u'子级', required=False,
                                        ondelete="set null")
    balance_parent_id = fields.Many2one('account.balance', u'父级', required=False, )

    voucher_line_ids = fields.One2many('account.voucher.line', 'balance_id', u'')
    begin = fields.Float(u'期初金额(原币)', digits='Amount')
    debit = fields.Float(u'本期借方(原币)', digits='Amount')
    credit = fields.Float(u'本期贷方(原币)', digits='Amount')
    balance = fields.Float(u'期末余额(原币)', digits='Amount')

    lc_begin = fields.Float(u'期初金额(本币)', digits='Amount')
    lc_debit = fields.Float(u'本期借方发生额(本币)', digits='Amount')
    lc_credit = fields.Float(u'本期贷方发生额(本币)', digits='Amount')
    lc_balance = fields.Float(u'期末余额(本币)', digits='Amount')

    lc_begin_debit = fields.Float(u'期初借方金额(本币)', digits='Amount')
    lc_begin_credit = fields.Float(u'期初贷方发金额(本币)', digits='Amount')
    lc_balance_debit = fields.Float(u'期末借方余额(本币)', digits='Amount')
    lc_balance_credit = fields.Float(u'期末贷方余额(本币)', digits='Amount')

    # 以下是做报表用栏
    a1 = fields.Float(u'当期(余额)', digits='Amount')
    b1 = fields.Float(u'前一期(余额)', digits='Amount')
    c1 = fields.Float(u'前二期(余额)', digits='Amount')
    d1 = fields.Float(u'去年同期(余额)', digits='Amount')
    p1 = fields.Float(u'前年同期(余额)', digits='Amount')

    e1 = fields.Float(u'年初余额', digits='Amount')
    f1 = fields.Float(u'去年年初余额', digits='Amount')
    s1 = fields.Float(u'前年年初余额', digits='Amount')

    g1 = fields.Float(u'年累余额', digits='Amount')
    h1 = fields.Float(u'去年同期年累余额', digits='Amount')
    q1 = fields.Float(u'前年同期年累余额', digits='Amount')

    i1 = fields.Float(u'当期(借-贷)', digits='Amount')
    j1 = fields.Float(u'前一期(借-贷)', digits='Amount')
    k1 = fields.Float(u'前二期(借-贷)', digits='Amount')
    l1 = fields.Float(u'去年同期(借-贷)', digits='Amount')
    r1 = fields.Float(u'前年同期(借-贷)', digits='Amount')

    m1 = fields.Float(u'年累(借-贷)', digits='Amount')
    n1 = fields.Float(u'去年同期年累(借-贷)', digits='Amount')
    o1 = fields.Float(u'前年同期年累(借-贷)', digits='Amount')

    lc_a1 = fields.Float(u'本币当期(余额)', digits='Amount')
    lc_b1 = fields.Float(u'本币前一期(余额)', digits='Amount')
    lc_c1 = fields.Float(u'本币前二期(余额)', digits='Amount')
    lc_d1 = fields.Float(u'本币去年同期(余额)', digits='Amount')
    lc_p1 = fields.Float(u'本币前年同期(余额)', digits='Amount')

    lc_e1 = fields.Float(u'本币年初(余额)', digits='Amount')
    lc_f1 = fields.Float(u'本币去年年初(余额)', digits='Amount')
    lc_s1 = fields.Float(u'前年年初余额', digits='Amount')

    lc_g1 = fields.Float(u'本币年累(余额)', digits='Amount')
    lc_h1 = fields.Float(u'本币去年年累(余额)', digits='Amount')
    lc_q1 = fields.Float(u'本币前年年累余额', digits='Amount')

    lc_i1 = fields.Float(u'当期(借-贷)', digits='Amount')
    lc_j1 = fields.Float(u'前一期(借-贷)', digits='Amount')
    lc_k1 = fields.Float(u'前二期(借-贷)', digits='Amount')
    lc_l1 = fields.Float(u'去年同期(借-贷)', digits='Amount')
    lc_r1 = fields.Float(u'前年同期(借-贷)', digits='Amount')

    lc_m1 = fields.Float(u'年累(借-贷)', digits='Amount')
    lc_n1 = fields.Float(u'去年年累(借-贷)', digits='Amount')
    lc_o1 = fields.Float(u'前年年累(借-贷)', digits='Amount')

    t1 = fields.Float(u'年累借', digits='Amount')
    u1 = fields.Float(u'年累贷', digits='Amount')
    lc_t1 = fields.Float(u'年累借(本币)', digits='Amount')
    lc_u1 = fields.Float(u'年累贷(本币)', digits='Amount')
